﻿/**********************************************
 * 功能名称：日报系统的报表生成类
编写人：  杨莹
建立时间： 杨 2009年8月8日
 *修改时间： 
说明：
 *建立日报系统的生成代码。

 **************************************************************/
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.Office.Interop.Excel;
using System.IO;
using Hisun.Models;
using Hisun.Controllers;

namespace Hisun.Controllers
{
    public class ScrbReport
    {
        //定义全局变量
        HisunDBDataContext db = new HisunDBDataContext();
        static Microsoft.Office.Interop.Excel.ApplicationClass myExcelApp; //Excel进程
        Microsoft.Office.Interop.Excel.Workbook myExcelBook;//Excel工作薄
        Microsoft.Office.Interop.Excel.Worksheet myExcelSheet;//Excel工作表



        //清空EXCEL应用、工作薄、工作表三个对象
        private void ClearThreeObject()
        {
            myExcelApp = null;
            myExcelBook = null;
            myExcelSheet = null;
        }


        private string[] ReturnfilePath(string UserName, string ModelReportName)
        {
            string[] MyfilePath = new string[] { "", "", "", "" };
            //建立样表路径，随机生成用户输出文件名，用户EXCEL表，用户HTML输出文件名
            MyfilePath[0] = HttpContext.Current.Server.MapPath(@"\Report\Sample\" + ModelReportName + ".xls");
            MyfilePath[1] = ModelReportName + UserName + "_" + DateTime.Now.GetHashCode().ToString();//ToString();      
            MyfilePath[2] = HttpContext.Current.Server.MapPath(@"\Report") + @"\UserReport\" + MyfilePath[1] + ".xls";
            MyfilePath[3] = HttpContext.Current.Server.MapPath(@"\Report") + @"\UserReport\" + MyfilePath[1] + ".html";

            if (!File.Exists(MyfilePath[0]))
            {
                //样表不存在
                MyfilePath[0] = "";
                MyfilePath[1] = "";
                MyfilePath[2] = "";
                MyfilePath[3] = "";


            }

            if (File.Exists(MyfilePath[2]))
            {
                File.Delete(MyfilePath[2]);
            }

            if (File.Exists(MyfilePath[3]))
            {
                File.Delete(MyfilePath[3]);
            }

            //挎出样表到临时输出表
            File.Copy(MyfilePath[0], MyfilePath[2]);

            return MyfilePath;

        }


        private void WriteReportStatistics(string RepName, string User, string Querystring, DateTime QueryTime)
        {
            var IsExist = from a in db.TABC_PUBLIC_ReportStatistics
                          where a.报表名称 == RepName
                          && a.操作员ID == User
                          && a.时间 == QueryTime
                          && a.查询内容 == Querystring
                          select a;
            if (IsExist.Count() == 0)
            {
                TABC_PUBLIC_ReportStatistics AddRow = new TABC_PUBLIC_ReportStatistics();
                AddRow.报表名称 = RepName;
                AddRow.操作员ID = User;
                AddRow.时间 = QueryTime;
                AddRow.查询内容 = Querystring;

                db.TABC_PUBLIC_ReportStatistics.InsertOnSubmit(AddRow);

                db.SubmitChanges();

            }



        }

        #region 例子
        private void ExcelConvertToHtml(Workbook MyWorkbook, string htmlPath)
        {
            //转移当前EXCEL工作薄为HTML，保存HTML文件。
            object fileName = htmlPath;
            object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;//Html   
            object o = System.Reflection.Missing.Value;
            MyWorkbook.SaveAs(fileName, format, o, o, o, o, XlSaveAsAccessMode.xlExclusive, o, o, o, o, o);
        }

        //一个制作报表的程序段。
        public string WriteExcel_Report_CenterList(int CenterId, string UserName)
        {

            string[] Myfile = ReturnfilePath(UserName, "交库成本明细表");
            string StrSamepleFile = Myfile[0];
            string StrReportName = Myfile[1];
            string StrReportFile = Myfile[2];
            string StrReportFileHtml = Myfile[3];


            ClearThreeObject();


            object Missing = System.Reflection.Missing.Value;
            myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            myExcelApp.Visible = false;
            myExcelApp.Workbooks._Open(StrReportFile, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
            myExcelBook = myExcelApp.Workbooks[1];
            myExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcelBook.ActiveSheet;



            try
            {

                var myQuerys1 = (from a in db.TABC_JS_CostCenter where a.上级作业中心ID == CenterId select a).ToArray();

                if (myQuerys1.Count() == 0)
                {
                    myExcelSheet.Cells[5, 1] = "没有满足查询的记录";

                }
                else
                {

                    myExcelSheet.Cells[2, 2] = "hisun";
                    myExcelSheet.Cells[3, 2] = myQuerys1[0].上级作业中心ID.ToString();
                    myExcelSheet.Cells[3, 4] = "测试";
                    int mySumCount = myQuerys1.Count();
                    int StartRow = 6;

                    for (int i = 0; i < mySumCount; i++)
                    {
                        myExcelSheet.Cells[StartRow + i, 1] = myQuerys1[i].作业中心ID.ToString();
                        myExcelSheet.Cells[StartRow + i, 2] = myQuerys1[i].作业中心名称.ToString();
                        myExcelSheet.Cells[StartRow + i, 3] = myQuerys1[i].类型.ToString();
                        myExcelSheet.Cells[StartRow + i, 4] = myQuerys1[i].是否直接成本.ToString();
                        myExcelSheet.Cells[StartRow + i, 5] = myQuerys1[i].成本细项ID.ToString();

                    }
                }



                ExcelConvertToHtml(myExcelBook, StrReportFileHtml);

                return StrReportName;

            }
            catch
            {
                return "-1";

            }
            finally
            {
                myExcelBook.Save();
                myExcelBook.Close(true, StrReportFile, true);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelApp);

                ClearThreeObject();
            }


        }

        #endregion


        private string splitbatch(string batch)
        {

            if (batch.IndexOf('/') > 0)
            {
                batch = batch.Split('/')[1];
            }

            return batch;
        }


        #region 生产日报系统之月份批信息查询。
        public string WriteExcel_Report_MonthQuery(int CenterId, string MyPIPId ,string Ap,string UserName)
        {

            string[] Myfile = ReturnfilePath(UserName, "Srcb_月批信息查询表");
            string StrSamepleFile = Myfile[0];
            string StrReportName = Myfile[1];
            string StrReportFile = Myfile[2];
            string StrReportFileHtml = Myfile[3];
        
            //写入报表查询记录
            WriteReportStatistics("Srcb_月批信息查询表", UserName, CenterId.ToString() + "-" + MyPIPId + "-" + Ap, DateTime.Now);
          
            ClearThreeObject();


            object Missing = System.Reflection.Missing.Value;
            myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            myExcelApp.Visible = false;
            myExcelApp.Workbooks._Open(StrReportFile, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
            myExcelBook = myExcelApp.Workbooks[1];
            myExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcelBook.ActiveSheet;



            try
            {

                //var myQuerys = from a in db.TSCRB_JS_BatchInfo
                //               where a.会计期间ID == Ap
                //               && a.中间体ID == MyPIPId
                //               select a;

                                    
                //插入日期。
                    var MyFirstDayAndLastDay = (from a in db.TABC_JS_AccountPeriod
                                     where a.会计期间ID == Ap
                                     select new 
                                     {
                                         a.成本开始日期,
                                         a.成本结账日期
                                     }).Single();

                    DateTime MyFirstDay = (DateTime)MyFirstDayAndLastDay.成本开始日期;
                    DateTime MylastDay = (DateTime)MyFirstDayAndLastDay.成本结账日期;
                    DateTime MyDay = MyFirstDay;
                    int i = 0;
                    while (MyDay <= MylastDay)
                    {
                        myExcelSheet.Cells[4, i + 3] = MyDay.Month.ToString() + "-" + MyDay.Day.ToString();
                        MyDay = MyDay.AddDays(1);
                        i++;
                    
                    }

                //查询出投入日期或是中止日期落在当前会计期间的批记录。
                    var myQuerys = from a in db.TSCRB_JS_BatchInfoDetail
                                   where (a.中间体ID == MyPIPId
                                   && a.投入日期 >= MyFirstDay && a.投入日期 <= MylastDay) ||
                                   (a.中间体ID == MyPIPId && a.中止日期 >= MyFirstDay && a.中止日期 <= MylastDay)
                                   orderby a.批号
                                   select a;

                var myCenterInfo = (from a in db.TABC_JS_CostCenter
                                   where a.作业中心ID == CenterId
                                   select new 
                                   { 
                                       a.作业中心ID, 
                                       a.作业中心名称 
                                   }).Single();

                var myPIPInfo = (from a in db.TABC_JS_ProductInProcess
                                    where a.中间体ID== MyPIPId
                                    select new 
                                    {
                                        a.中间体ID, 
                                        a.中间体名称
                                    }).Single();


                if (myQuerys.Count() == 0)
                {
                    myExcelSheet.Cells[5, 1] = "无信息";

                }
                else
                {

                    myExcelSheet.Cells[1, 1] = "月度批流转信息表" + "(" + Ap + ")";
                    myExcelSheet.Cells[2, 1] = "作业中心：" + "(" + myCenterInfo.作业中心ID+")"+myCenterInfo.作业中心名称;
                    myExcelSheet.Cells[3, 1] = "中间体：" + "(" + myPIPInfo.中间体ID + ")" + myPIPInfo.中间体名称;

                    int InsertRowNum = myQuerys.Count();


                    Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myExcelSheet.Rows[6, Missing];
                    for (int k = 0; k < InsertRowNum; k++)
                    {
                        myRange.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Missing);
                    }
                    myRange = null;


                    int StartRow = 5;
                    int j = 0;

                    foreach (var MyRow in myQuerys)
                    {

                        myExcelSheet.Cells[StartRow + j, 1] = splitbatch(MyRow.批号);
                        if (MyRow.原批号 == null)
                        { myExcelSheet.Cells[StartRow + j, 2] = "无原批号"; }
                        else
                        { myExcelSheet.Cells[StartRow + j, 2] = splitbatch(MyRow.原批号); }
                      

                        var MyRowDetails = from a in db.TSCRB_JS_BatchInfoDetail
                                          where
                                          //a.会计期间ID == Ap
                                           a.中间体ID == MyPIPId
                                          && a.批号 == MyRow.批号
                                          select new
                                          {
                                              a.批号,
                                              a.投入日期,
                                              a.中止日期,
                                              a.原批号

                                          };
                        if (MyRowDetails.Count() > 0)
                        {
                            foreach (var MyRowDetail in MyRowDetails)
                            {
                                TimeSpan MyDayBalance1 = (TimeSpan)(MyRowDetail.投入日期 - MyFirstDay);
                               int Spanday1 = MyDayBalance1.Days;
                               if (Spanday1 < 0)
                               { myExcelSheet.Cells[StartRow + j,  3] = "上期投入";


                               if (MyRowDetail.中止日期 != null)
                               {
                                   TimeSpan MyDayBalance2 = (TimeSpan)(MyRowDetail.中止日期 - MyFirstDay);
                                   int Spanday2 = MyDayBalance2.Days;
                                   if (Spanday2 < 0)
                                   {
                                       myExcelSheet.Cells[StartRow + j, 3] = "上期投入";
                                   }
                                   else
                                   {
                                       if (Spanday2 == Spanday1)
                                       {
                                           myExcelSheet.Cells[StartRow + j, Spanday2 + 3] = "投入/终止";
                                       }
                                       else
                                       {
                                           TimeSpan MyDayBalance3 = (TimeSpan)(MyRowDetail.中止日期 - MylastDay);
                                           int Spanday3 = MyDayBalance3.Days;
                                           if (Spanday3 > 0)
                                           {
                                               myExcelSheet.Cells[StartRow + j, Spanday2 - Spanday3 + 3] = "跨期";

                                               Microsoft.Office.Interop.Excel.Range myRange1 = (Microsoft.Office.Interop.Excel.Range)myExcelSheet.get_Range(myExcelSheet.Cells[StartRow + j, 3], myExcelSheet.Cells[StartRow + j, Spanday2 - Spanday3 + 3]);
                                               myRange1.Interior.ColorIndex = 19;
                                               myRange1 = null;

                                           }
                                           else
                                           {
                                               myExcelSheet.Cells[StartRow + j, Spanday2 + 3] = "终止";


                                               Microsoft.Office.Interop.Excel.Range myRange1 = (Microsoft.Office.Interop.Excel.Range)myExcelSheet.get_Range(myExcelSheet.Cells[StartRow + j, 3], myExcelSheet.Cells[StartRow + j, Spanday2 + 3]);
                                               myRange1.Interior.ColorIndex = 19;
                                               myRange1 = null;

                                           }
                                       }


                                   }
                               }
                               
                               }
                               else  // 当投入日期在当前会计期间时
                               { myExcelSheet.Cells[StartRow + j, Spanday1 + 3] = "投入";


                               if (MyRowDetail.中止日期 != null)
                               {
                                   TimeSpan MyDayBalance2 = (TimeSpan)(MyRowDetail.中止日期 - MyFirstDay);
                                   int Spanday2 = MyDayBalance2.Days;
                                   if (Spanday2 < 0)
                                   {
                                       myExcelSheet.Cells[StartRow + j, 3] = "上期投入";
                                   }
                                   else
                                   {
                                       if (Spanday2 == Spanday1)
                                       {
                                           myExcelSheet.Cells[StartRow + j, Spanday2 + 3] = "投入/终止";
                                       }
                                       else
                                       {
                                           TimeSpan MyDayBalance3 = (TimeSpan)(MyRowDetail.中止日期 - MylastDay);
                                           int Spanday3 = MyDayBalance3.Days;
                                           if (Spanday3 > 0)
                                           {
                                               myExcelSheet.Cells[StartRow + j, Spanday2 - Spanday3 + 3] = "";

                                               Microsoft.Office.Interop.Excel.Range myRange1 = (Microsoft.Office.Interop.Excel.Range)myExcelSheet.get_Range(myExcelSheet.Cells[StartRow + j, Spanday1 + 3], myExcelSheet.Cells[StartRow + j, Spanday2 - Spanday3 + 3]);
                                               myRange1.Interior.ColorIndex = 19;
                                               myRange1 = null;

                                           }
                                           else
                                           {
                                               myExcelSheet.Cells[StartRow + j, Spanday2 + 3] = "终止";


                                               Microsoft.Office.Interop.Excel.Range myRange1 = (Microsoft.Office.Interop.Excel.Range)myExcelSheet.get_Range(myExcelSheet.Cells[StartRow + j, Spanday1 + 3], myExcelSheet.Cells[StartRow + j, Spanday2 + 3]);
                                               myRange1.Interior.ColorIndex = 19;
                                               myRange1 = null;

                                           }
                                       }


                                   }
                               }
                               
                               
                               
                               }


                            }
                        
                        }

                        j++;

                    }

                    Range range = myExcelSheet.get_Range(("A"+(StartRow + j+ 2).ToString()).ToString(), Missing);
                    Hyperlink hyperlink = (Hyperlink)range.Hyperlinks.Add(range, @"javascript:history.back();", Missing, Missing, "返回");


                }


                myExcelBook.Save();
                ExcelConvertToHtml(myExcelBook, StrReportFileHtml);

                return StrReportName;

            }
            catch
            {
                return "-1";

            }
            finally
            {
                myExcelBook.Save();
                myExcelBook.Close(true, StrReportFile, true);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelApp);

                ClearThreeObject();
            }


        }

        #endregion

        #region 生产日报系统之月份批转出查询。
        public string WriteExcel_Report_MonthOutQuery(int CenterId, string MyPIPId, string Ap, string UserName)
        {

            string[] Myfile = ReturnfilePath(UserName, "Srcb_月转出数量查询表");
            string StrSamepleFile = Myfile[0];
            string StrReportName = Myfile[1];
            string StrReportFile = Myfile[2];
            string StrReportFileHtml = Myfile[3];

            //写入报表查询记录
            WriteReportStatistics("Srcb_月转出数量查询表", UserName, CenterId.ToString() + "-" + MyPIPId + "-" + Ap, DateTime.Now);
         
            ClearThreeObject();


            object Missing = System.Reflection.Missing.Value;
            myExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            myExcelApp.Visible = false;
            myExcelApp.Workbooks._Open(StrReportFile, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing);
            myExcelBook = myExcelApp.Workbooks[1];
            myExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcelBook.ActiveSheet;



            try
            {

            

                var MyOutIndexSum = from a in db.TSCRB_JS_Out
                               where a.会计期间ID == Ap
                               && a.中间体ID == MyPIPId
                               group a by a.批号 into g
                               select new
                               {
                                   批号 = g.Key,
                                   转出总数 = g.Sum(p=>p.转出系数)

                               };

                var myQuerys = from a in db.TSCRB_JS_Out
                               join b in MyOutIndexSum on a.批号 equals b.批号
                               into temps
                               from temp in temps.DefaultIfEmpty()
                               where a.会计期间ID == Ap
                               && a.中间体ID == MyPIPId  orderby a.转出时间,a.批号
                               select new 
                               {
                                 a.批号,
                                 a.转出时间,
                                 a.转出数量,
                                 a.转出系数,
                                 是否全部转出 = ((temp.转出总数 == null || temp.转出总数 > 1 || temp.转出总数 <= 0)?"无效系数":(temp.转出总数 < 1?"否":"是"))
                               
                               };

                var myCenterInfo = (from a in db.TABC_JS_CostCenter
                                    where a.作业中心ID == CenterId
                                    select new
                                    {
                                        a.作业中心ID,
                                        a.作业中心名称
                                    }).Single();

                var myPIPInfo = (from a in db.TABC_JS_ProductInProcess
                                 where a.中间体ID == MyPIPId
                                 select new
                                 {
                                     a.中间体ID,
                                     a.中间体名称,
                                     a.计量单位
                                 }).Single();


                if (myQuerys.Count() == 0)
                {
                    myExcelSheet.Cells[5, 1] = "没有满足查询的记录";

                    return "-1";


                }
                else
                {

                    myExcelSheet.Cells[1, 1] = "月转出数量查询表"+"("+Ap+")";
                    myExcelSheet.Cells[2, 1] = "作业中心：" + "(" + myCenterInfo.作业中心ID + ")" + myCenterInfo.作业中心名称;
                    myExcelSheet.Cells[3, 1] = "中间体：" + "(" + myPIPInfo.中间体ID + ")" + myPIPInfo.中间体名称;
                    myExcelSheet.Cells[3, 5] = "单位：" + myPIPInfo.计量单位;
                    int InsertRowNum = myQuerys.Count();


                    Microsoft.Office.Interop.Excel.Range myRange = (Microsoft.Office.Interop.Excel.Range)myExcelSheet.Rows[6, Missing];
                    for (int k = 0; k < InsertRowNum; k++)
                    {
                        myRange.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Missing);
                    }
                    myRange = null;



                    int StartRow = 5;
                    int j = 0;

                    foreach (var MyRow in myQuerys)
                    {

                        myExcelSheet.Cells[StartRow + j, 1] = splitbatch(MyRow.批号);
                        myExcelSheet.Cells[StartRow + j, 2] = MyRow.转出时间;
                        myExcelSheet.Cells[StartRow + j, 3] = MyRow.转出系数;
                        myExcelSheet.Cells[StartRow + j, 4] = MyRow.是否全部转出;
                        myExcelSheet.Cells[StartRow + j, 5] = MyRow.转出数量;

     

                        j++;

                    }

                    Range range = myExcelSheet.get_Range(("A" + (StartRow + j + 3).ToString()).ToString(), Missing);
                    Hyperlink hyperlink = (Hyperlink)range.Hyperlinks.Add(range, @"javascript:history.back();", Missing, Missing, "返回");


                }


                myExcelBook.Save();
                ExcelConvertToHtml(myExcelBook, StrReportFileHtml);

                return StrReportName;

            }
            catch
            {
                return "-1";

            }
            finally
            {
                myExcelBook.Save();
                myExcelBook.Close(true, StrReportFile, true);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcelApp);

                ClearThreeObject();
            }


        }

        #endregion
    }
}
